# 1.定义一个方法
import pymysql
from pymysql.constants import CLIENT


def exe_sql(sql_str, database=None):
    """

    :param sql_str: 执行的SQl语句
    :param database: 数据库名称
    :return: 返回修改信息
    """
    conn = None
    cur = None
    try:
        # 2.创建数据库连接对象
        conn = pymysql.connect(host="121.43.169.97", user="root", password="Itcast_p2p_20191228", database=database,
                               client_flag=CLIENT.MULTI_STATEMENTS)

        # 3.创建游标对象
        cur = conn.cursor()

        # 4.在游标对象中执行SQL语句
        cur.execute(sql_str)

        # 如果是查询语句
        if sql_str.split(" ")[0].lower() == 'select':
            # 返回全部查询结果
            return cur.fetchall()

        # 如果是非查询语句
        else:
            # 手动提交数据事务
            conn.commit()
            # 返回受影响的行数
            # return f'受影响的行数：{cur.rowcount}'
            print(f'受影响的行数：{cur.rowcount}')
    except Exception as e:
        # 有异常的情况下，进行数据库事务回滚
        if conn is not None:
            conn.rollback()
            print(e)
    finally:
        # 5.关闭游标对象
        if cur is not None:
            cur.close()

        # 6.关闭数据库连接对象
        if conn is not None:
            conn.close()


# 封装清洗用户信息的方法
def clear_user_info():
    """
    执行SQL
    """
    # 需要执行SQL语句
    sql1 = "delete i.* from mb_member_info i inner join mb_member m on m.id = i.member_id where m.phone in (" \
           "'18708309001','18708309002','18708309003','18708309004','18708309005','18708309006','18708309007'," \
           "'18708309008','18708309009','18708309010'); "
    sql2 = "delete l.* from mb_member_login_log l inner join mb_member m on m.id=l.member_id where m.phone in (" \
           "'18708309001','18708309002','18708309003','18708309004','18708309005','18708309006','18708309007'," \
           "'18708309008','18708309009','18708309010') "
    sql3 = "delete from mb_member where phone in ('18708309001','18708309002','18708309003','18708309004'," \
           "'18708309005','18708309006','18708309007','18708309008','18708309009','18708309010') "
    sql4 = "delete from mb_member_register_log where phone in ('18708309001','18708309002','18708309003'," \
           "'18708309004','18708309005','18708309006','18708309007','18708309008','18708309009','18708309010') "
    # 调用封装操作数据库的方法，执行SQL语句
    exe_sql(sql1, "czbk_member")
    exe_sql(sql2, "czbk_member")
    exe_sql(sql3, "czbk_member")
    exe_sql(sql4, "czbk_member")


# 封装初始化借款标数据的方法
def init_tender_info():
    """

    :return:
    """
    # SQL语句
    sql = '''delete from `czbk_member`.`mb_member` where id=869; delete from `czbk_member`.`mb_member_info` where 
    id=987; delete from `czbk_finance`.`fn_loan_amount` where id=987; delete from `p2p_mock`.`p2p_account` where 
    id=911; delete from `czbk_finance`.`fn_loan` where id=642; delete from `czbk_finance`.`fn_loan_info` where 
    id=832; delete from `czbk_finance`.`fn_loan_amount_log` where id=869; INSERT INTO `czbk_member`.`mb_member` (
    `id`, `name`, `trust_account`, `password`, `email`, `phone`, `paypassword`, `count`, `register_ip`, 
    `register_time`, `register_date`, `lastlogin_ip`, `lastlogin_time`, `lastlogin_date`, `register_type`, `role`, 
    `group`, `group_status`, `is_realname`, `is_email`, `is_phone`, `is_video`, `is_id5`, `is_vip`, `credit_point`, 
    `is_auto`, `pwd_attach`, `status`, `lock_time`, `vip_start_time`, `vip_end_time`, `vip_category_id`, 
    `vip_category_ind`, `amount`, `phonestr`) VALUES ('869', '13099775533', '6021234507160403', 
    'eab70f4a46c640b3dfc35040bf37c803', NULL, '13099775533', NULL, '1', '1972826569', '1588835827', '2020-05-07', 
    '1972826569', '1588835827', '2020-05-07', '1', '1', '1', '2', '1', '-1', '1', '-1', '-1', '-1', '0', '-1', 
    '1m4U82PqPx', '1', NULL, NULL, NULL, NULL, NULL, '0.00', NULL); INSERT INTO `czbk_member`.`mb_member_info` (`id`, 
    `member_id`, `member_name`, `avatar`, `nickname`, `realname`, `gender`, `birthday`, `card_id`, `qq`, 
    `educational_background`, `marry_status`, `graduated`, `company_industry`, `company_scale`, `company_office`, 
    `monthly_income`, `invite_money`, `hometown_province`, `hometown_city`, `hometown_area`, `hometown_post`) VALUES 
    ('987', '869', '13099775533', NULL, NULL, '小石头', '男', '1986-11-18', '429009118611181111', NULL, NULL, NULL, NULL, 
    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL); INSERT INTO `czbk_finance`.`fn_loan_amount` (`id`, 
    `member_id`, `member_name`, `credit_amount`, `credit_amount_freeze`, `vouch_amount`, `vouch_amount_freeze`, 
    `pawn_amount`, `pawn_amount_freeze`, `roam_amount`, `roam_amount_freeze`) VALUES ('987', '869', '13099775533', 
    '200000.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000'); INSERT INTO 
    `p2p_mock`.`p2p_account` (`id`, `account`, `AvlBal`, `AcctBal`, `FrzBal`, `create_time`) VALUES ('911', 
    '6021234507160403', '0.00', '0.00', '0.00', '2020-05-07 16:04:03'); INSERT INTO `czbk_finance`.`fn_loan` (`id`, 
    `ind`, `serialno`, `name`, `member_id`, `member_name`, `amount`, `credited_amount`, `progress`, `tender_count`, 
    `category_id`, `category_type`, `repay_type`, `period`, `apr`, `sort_index`, `sort_top`, `status`, 
    `hidden_status`, `additional_status`, `deposit_certificate`, `certificate_file_id`, `loan_repay_status`, 
    `loan_repay_time`, `overdue_time`, `add_date`, `add_time`, `verify_time`, `reverify_time`, `add_ip`, 
    `vouch_company_id`, `op_status`, `marker_type`) VALUES ('642', 'f5380d59791773a8abad4141ff90a5a4', 
    '202008231111', '数据库构造借款1', '869', '13099775533', '5000.00', '0.00', '0.00', '0', '1', '1', '1', '60', '7.55', 
    '1', NULL, '3', '1', '-1', '-1', NULL, NULL, NULL, '1640962392', '2020-05-07', '1588847800', '1588851413', NULL, 
    '1972826569', '0', '-1', '数据库'); INSERT INTO `czbk_finance`.`fn_loan_info` (`id`, `loan_id`, `thumbs`, 
    `contents`, `attachment_ids`, `password`, `use`, `tender_amount_min`, `tender_amount_max`, `freeze_amount`, 
    `freeze_amount_proportion`, `freeze_period`, `award_status`, `fail_award_status`, `award_amount`, 
    `award_proportion`, `award_amount_total`, `validate`, `part_status`, `tender_count`, `comment_status`, 
    `comment_count`, `is_company`, `company_name`, `vouch_company_info`, `vouch_company_pic`, 
    `vouch_company_guaranty`, `amount_category_id`, `hits`, `cancel_admin_id`, `cancel_remark`, `cancel_time`, 
    `verify_admin_id`, `verify_admin_name`, `verify_remark`, `verify_time`, `verify_ip`, `reverify_admin_id`, 
    `reverify_admin_name`, `reverify_remark`, `reverify_time`, `reverify_ip`, `auto_scale`, `is_auto`, 
    `additional_status`, `additional_apr`, `additional_name`, `additional_amount_max`, `additional_pic`, 
    `product_process`, `information`) VALUES ('832', '642', NULL, '数据库构造借款1', 'a:0:{}', NULL, '10102', '100.00', 
    '1000.00', NULL, '10.00', NULL, '-1', '-1', NULL, NULL, NULL, '239', NULL, '0', '-1', '0', '-1', NULL, NULL, 
    NULL, NULL, '1', '1', NULL, NULL, NULL, '4', 'admin', '数据库构造借款1 通过', '1588851413', '1972826569', NULL, NULL, 
    NULL, NULL, NULL, NULL, '1', '-1', '0.000000', NULL, '0.000000', NULL, NULL, NULL); UPDATE 
    `czbk_finance`.`fn_loan_amount` SET `credit_amount_freeze`='100000.000000' WHERE `member_id`='869'; INSERT INTO 
    `czbk_finance`.`fn_loan_amount_log` (`member_id`, `member_name`, `category_id`, `type`, `amount`, `remark`, 
    `add_time`) VALUES ('869','13099775533', 1, 4, '100000', '借款冻结', '1972826569'); '''
    # 执行SQL语句
    res = exe_sql(sql)
    return res


if __name__ == '__main__':
    clear_user_info()
    init_tender_info()
